package cn.topevery.garden.framework.base;


import cn.topevery.garden.framework.Database;
import hw.topevery.framework.*;
import hw.topevery.framework.annotation.DbTableField;
import hw.topevery.framework.db.*;
import hw.topevery.framework.db.base.BaseEntityDaoImpl;
import hw.topevery.framework.db.entity.*;
import hw.topevery.framework.db.enums.ScriptConditionEnum;
import hw.topevery.framework.entity.DbCsp;
import hw.topevery.framework.entity.DbTableMate;
import hw.topevery.framework.entity.KeyValue;
import hw.topevery.framework.entity.Value;
import hw.topevery.framework.util.LogUtil;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.locationtech.jts.geom.Geometry;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;

public abstract class BaseDaoImpl<T> extends BaseEntityDaoImpl<T, Integer> {
    @Override
    public DbExecute getDbExecute() {
        return Database.pmiDbExecute;
    }

    @Override
    public <P extends DbCsp> List<T> getSearch(P p) {
        SqlQueryMate sqlQueryMate = getSelectMate();
        String sql = sqlQueryMate.getSql();
        return getList(sql, CommandType.Text, null, getEntityClass());
    }

    // region 数据库CUD(创建/更新/删除)操作

    @Override
    public void updateBefore(String userId, T val) {
        if (val instanceof BaseEntity) {
            BaseEntity entity = (BaseEntity) val;
            entity.updateId = userId;
            entity.updateTime = LocalDateTime.now();
        }
        super.updateBefore(userId, val);
    }

    /**
     * 新增自动填充字段
     *
     * @param val 要插入的数据
     */
    @Override
    public void insertBefore(String userId, T val) {
        if (val instanceof BaseEntity) {
            // 如果是BaseEntity子类，insert时自动填充字段
            LocalDateTime nowTime = LocalDateTime.now();
            BaseEntity entity = (BaseEntity) val;
            entity.createId = userId;
            entity.createTime = nowTime;
            entity.updateId = userId;
            entity.updateTime = nowTime;
            entity.dbStatus = false;
        }

        super.insertBefore(userId, val);
    }


    @Override
    public void setDeleteField(String userId, T val) {
        if (val instanceof BaseEntity) {
            BaseEntity o = (BaseEntity) val;
            o.updateId = userId;
            o.updateTime = LocalDateTime.now();
            o.dbStatus = true;
        }
    }

    /**
     * 插入一条数据
     *
     * @param vals 数据实体
     */
    @Override
    public void insert(String userId, List<T> vals) {
        insert(userId, (T[]) vals.toArray());
    }

    /**
     * 插入一条数据
     *
     * @param val 数据实体
     */
    public void insertFillIdentity(String userId, T val) {

/*        insertBefore(userId, val);
        BaseEntity o = (BaseEntity) val;

        SqlMate sqlMate = DbScriptHelper.getInsertSqlMate(getDatabaseType(),getDbPrefix(), getEntityClass(), val);
        sqlMate.sql.append("select @@IDENTITY as id;");
        run(sqlMate.sql.toString(), CommandType.Text, dbCmd -> {
            for (DbParameter j : sqlMate.parameters) {
                dbCmd.addParameter(j);
            }
            DbDataReader dr = dbCmd.executeReader();
            if (dr.read()) {
                o.id = dr.getInt("id");
            }
        });*/

        removeCacheData();
    }

    public T insertEntity(String userId, T val) {

/*        insertBefore(userId, val);
        BaseEntity o = (BaseEntity) val;

        SqlMate sqlMate = DbScriptHelper.getInsertSqlMate(getDatabaseType(),getDbPrefix(), getEntityClass(), val);
        sqlMate.sql.append("select @@IDENTITY as id;");
        run(sqlMate.sql.toString(), CommandType.Text, dbCmd -> {
            for (DbParameter j : sqlMate.parameters) {
                dbCmd.addParameter(j);
            }
            DbDataReader dr = dbCmd.executeReader();
            if (dr.read()) {
                o.id = dr.getInt("id");
            }
        });
        removeCacheData();*/
        return val;
    }

    public void saveOrUpdate(String userId, T entity) {
        BaseEntity o = (BaseEntity) entity;
        if (o.id != null && o.id > 0) {
            update(userId, entity);
        } else {
            insertFillIdentity(userId, entity);
        }
    }


    public T insertOrUpdate(String userId, T entity) {
        BaseEntity o = (BaseEntity) entity;
        if (o.id != null && o.id > 0) {
            update(userId, entity);
            return entity;
        } else {
            insertEntity(userId, entity);
            return entity;
        }
    }

    /**
     * 新增或更新
     *
     * @param entityList
     */
    public void saveOrUpdate(String userId, T... entityList) {
        if (StringUtils.isEmpty(userId)) {
            userId = SystemConst.UUID_EMPTY_STRING;
        }

        String finalUserId = userId;
        Database.pmiDbExecute.transactionScope(() -> {
            if (ObjectUtils.isNotEmpty(entityList) && entityList.length > 0) {
                List<T> insertList = new ArrayList<>();
                List<T> updateList = new ArrayList<>();
                for (T entity : entityList) {
                    Class<?> entityClass = entity.getClass();
                    Field[] fieldArr = entityClass.getFields();
                    for (Field field : fieldArr) {
                        field.setAccessible(true);

                        // 寻找实体类上的主键ID
                        DbTableField annotation = field.getAnnotation(DbTableField.class);
                        if (ObjectUtils.isNotEmpty(annotation) && annotation.isKey()) {
                            try {
                                Object id = field.get(entity);
                                if (entity instanceof BaseEntity) {
                                    if (ObjectUtils.isEmpty(id) || id.equals(0)) {
                                        ((BaseEntity) entity).createId = finalUserId;
                                        ((BaseEntity) entity).updateId = finalUserId;
                                        insertList.add(entity);
                                    } else {
                                        ((BaseEntity) entity).updateId = finalUserId;
                                        updateList.add(entity);
                                    }
                                }
                            } catch (IllegalAccessException e) {
                                LogUtil.error("没有找到主键ID:", e);
                                throw new RuntimeException("没有找到主键ID");
                            }
                            break;
                        }
                    }
                }

                if (insertList.size() > 0) {
                    insert(finalUserId, insertList);
                }
                if (updateList.size() > 0) {
                    update(finalUserId, updateList);
                }
            }
        });
    }

    // endregion


    // region 数据R(读取)操作


    // endregion

    /**
     * @param colKeyValue 列值对应
     * @return
     */
    protected T getEntityByColumnEqValue(KeyValue<?> colKeyValue) {
        Value<T> res = new Value<>();
        SqlQueryMate sqlQueryMate = getSelectMate();
        sqlQueryMate.where(true, new KeyValueCondition(colKeyValue.key, ScriptConditionEnum.Eq, colKeyValue.value));
        String sql = sqlQueryMate.getSql();
        run(sql, CommandType.Text, dbCmd -> {
            for (DbParameter i : sqlQueryMate.getParameters()) {
                dbCmd.addParameter(i);
            }
            res.data = dbCmd.executeToEntity(getEntityClass());
        });
        return res.data;
    }

    /**
     * 查询单个对象
     *
     * @param sqlQueryMate sqlQueryMate
     * @param target       目标类
     * @param <E>          泛型（目标类型）
     * @return 标类的数据
     */
    public <E> E selectOne(SqlQueryMate sqlQueryMate, Class<E> target) {
        Value<E> res = new Value<>();
        run(sqlQueryMate.getSql(), CommandType.Text, dbCmd -> {
            sqlQueryMate.getParameters().forEach(dbCmd::addParameter);
            res.data = dbCmd.executeToEntity(target);
        });
        return res.data;
    }

    /**
     * 查询集合
     *
     * @param sqlQueryMate sqlQueryMate
     * @param target       目标类
     * @param <R>          泛型（目标类型）
     * @return 标类的数据
     */
    public <R> List<R> selectList(SqlQueryMate sqlQueryMate, Class<R> target) {
        List<R> res = new ArrayList<>();
        run(sqlQueryMate.getSql(), CommandType.Text, dbCmd -> {
            sqlQueryMate.getParameters().forEach(dbCmd::addParameter);
            dbCmd.executeToList(res, target);
        });
        return res;
    }

    protected <T> List<T> getList(String sqlText, CommandType commandType, List<KeyValue<?>> parameters, Class<T> entityClass) {
        List<T> res = new ArrayList<>();
        run(sqlText, commandType, dbCmd -> {
            for (KeyValue parameter : parameters) {
                dbCmd.addInParameter(parameter.key, parameter.value);
            }
            dbCmd.executeToList(res, entityClass);
        });
        return res;
    }

    /**
     * 获取目标类的数据
     *
     * @param sql         sql 语句
     * @param commandType 执行类型
     * @param keyValues   参数集合
     * @param target      目标类
     * @param <R>         泛型（目标类型）
     * @return 标类的数据
     */
    public <R> DataPager<R> getDataPagerByCustomize(String sql, CommandType commandType,
                                                    List<KeyValue<Object>> keyValues, Class<R> target) {
        Value<DataPager<R>> result = new Value<>();
        run(sql, commandType, dbCmd -> {
            if (keyValues != null && keyValues.size() != 0) {
                keyValues.forEach(keyValue -> dbCmd.addInParameter(keyValue.key, keyValue.value));
            }
            result.data = dbCmd.executeToDataPager(target);
        });

        return result.data;
    }

    /**
     * 获取目标类的数据
     *
     * @param target 目标类
     * @param <E>    泛型（目标类型）
     * @return 标类的数据
     */
    public <E> DataPager<E> getDataPager(SqlQueryMate sqlQueryMate, int pageSize, int pageIndex, Class<E> target) {
        Value<DataPager<E>> result = new Value<>();
        run(sqlQueryMate.getSqlPage(pageSize, pageIndex), CommandType.Text, dbCmd -> {
            sqlQueryMate.getParameters().forEach(parameter -> {
                dbCmd.addParameter(parameter);
            });
            result.data = dbCmd.executeToDataPager(target);
        });
        return result.data;
    }

    public Map<String, Object> getOneMap(SqlQueryMate sqlQueryMate) {
        List<Map<String, Object>> result = getMap(sqlQueryMate);
        return result.size() > 0 ? result.get(0) : null;
    }

    public Map<String, Object> getOneMap(String sql) {
        List<Map<String, Object>> result = getMap(sql);
        return result.size() > 0 ? result.get(0) : null;
    }

    public List<Map<String, Object>> getMap(String sql) {
        List<Map<String, Object>> mapList = new ArrayList<>();
        run(sql, CommandType.Text, dbCmd -> {
            ResultSet resultSet = null;
            try {
                PreparedStatement preparedStatement = dbCmd.getConnection().prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                Integer columnCount = resultSet.getMetaData().getColumnCount();
                while (resultSet.next()) {
                    Map<String, Object> map = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String name = resultSet.getMetaData().getColumnTypeName(i);
                        Object value = resultSet.getObject(i);
                        if (name.equalsIgnoreCase("geometry")) {
                            value = GeometryHelper.convertGeometryToPoint((Geometry) ChangeTypeHelper.changeType(value, Geometry.class));
                        }
                        map.put(resultSet.getMetaData().getColumnLabel(i), value);
                    }
                    mapList.add(map);
                }

                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                try {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        });
        return mapList;
    }


    public List<Map<String, Object>> getMap(SqlQueryMate sqlQueryMate) {
        List<Map<String, Object>> mapList = new ArrayList<>();
        run(sqlQueryMate.getSql(), CommandType.Text, dbCmd -> {
            sqlQueryMate.getParameters().forEach(dbCmd::addParameter);
            DbDataReader dbDataReader = dbCmd.executeReader();
            try {
                Integer columnCount = dbDataReader.getMetaData().getColumnCount();
                while (dbDataReader.read()) {
                    Map<String, Object> map = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String name = dbDataReader.getMetaData().getColumnTypeName(i);
                        Object value = dbDataReader.getObject(i);
                        if (name.equalsIgnoreCase("geometry")) {
                            value = GeometryHelper.convertGeometryToPoint((Geometry) ChangeTypeHelper.changeType(value, Geometry.class));
                        }
                        map.put(dbDataReader.getMetaData().getColumnLabel(i), value);
                    }
                    mapList.add(map);
                }
            } catch (Exception exception) {
                exception.printStackTrace();
            } finally {
                if (dbDataReader != null) {
                    dbDataReader.close();
                }
            }
        });
        return mapList;
    }

    @Deprecated
    public <R> R getDataByCustomize(String sql, CommandType commandType, List<KeyValue<Object>> keyValues, Class<R> target) {
        Value<R> result = new Value();
        this.run(sql, commandType, (dbCmd) -> {
            if (keyValues != null && keyValues.size() != 0) {
                keyValues.forEach((keyValue) -> {
                    dbCmd.addInParameter(keyValue.key, keyValue.value);
                });
            }

            result.data = dbCmd.executeToEntity(target);
        });
        return result.data;
    }


    public <R> List<R> selectListByProcedure(String procedure, Map<String, Object> params, Class<R> target) {
        DataPager<R> result = new DataPager();
        this.run(procedure, CommandType.StoredProcedure, (dbCmd) -> {
            if (params != null && params.size() != 0) {
                Objects.requireNonNull(dbCmd);
                params.forEach(dbCmd::addInParameter);
            }

            this.fillResultSet(target, result, dbCmd);
        });
        return result.records;
    }


    private <R> void fillResultSet(Class<R> target, DataPager<R> result, DbCommand dbCmd) {
//        Assert.notNull(target, () -> {
//            return "this target class is required, it must not be null";
//        });
     /*   DbDataReader dr = dbCmd.executeReader();

        try {
            SqlRowSetMetaData metaData = dr.getMetaData();
            TypeHandler<?> typeHandler = TypeHandlerRegistry.getInstance().getTypeHandler(target);
            if (metaData.getColumnNames().length == 1 && typeHandler != null) {
                while (dr.next()) {
                    Object value = typeHandler.getNullableResult(dr, 1);
                    if (value != null) {
                        //result.records.add(value);
                    }
                }
            } else {
                DataFillHelper.readToList(dr, result.records, target);
            }

            if (dr.nextResult() && dr.read()) {
                result.total = (long) dr.getInt(1);
            }
        } catch (Throwable var9) {
            if (dr != null) {
                try {
                    dr.close();
                } catch (Throwable var8) {
                    var9.addSuppressed(var8);
                }
            }

            throw var9;
        }

        if (dr != null) {
            dr.close();
        }*/

    }

    /**
     * 查询总记录数(可以根据条件)
     *
     * @param sqlQueryMate sqlQueryMate
     * @return 总记录数
     */
    public int count(SqlQueryMate sqlQueryMate) {
        sqlQueryMate.setColumns("count(*)");
        Value<Integer> value = new Value<>();
        run(sqlQueryMate.getSql(), CommandType.Text, dbCmd -> {
            sqlQueryMate.getParameters().forEach(dbCmd::addParameter);
            DbDataReader dr = dbCmd.executeReader();
            if (dr.read()) {
                value.data = dr.getInt(1);
            }
        });

        return ObjectUtils.isEmpty(value.data) ? 0 : value.data;
    }

    /**
     * 调用存储过程返回map
     *
     * @param procedure 存储过程名称
     * @param param     参数
     * @return
     */
    public List<Map<String, Object>> getMapListForProcedure(String procedure, Map<String, Object> param) {
        List<Map<String, Object>> list = new ArrayList<>();
        run(procedure, CommandType.StoredProcedure, dbCommand -> {
            Set<String> strings = param.keySet();
            for (String string : strings) {
                dbCommand.addInParameter(string, param.get(string));
            }
            DbDataReader dbDataReader = dbCommand.executeReader();
            try {
                int columnCount = dbDataReader.getMetaData().getColumnCount();
                while (dbDataReader.read()) {
                    Map<String, Object> map = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        Object value = dbDataReader.getObject(i);
                        map.put(dbDataReader.getMetaData().getColumnLabel(i), value);
                    }
                    list.add(map);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            } finally {
                if (dbDataReader != null) {
                    dbDataReader.close();
                }
            }
        });
        return list;
    }

    /**
     * 调用存储过程返回map数组
     *
     * @param procedure 存储过程名称
     * @param param     参数
     * @return
     */
    public List<List<Map<String, Object>>> getMapListsForProcedure(String procedure, Map<String, Object> param) {
        List<List<Map<String, Object>>> resultList = new ArrayList<>();
        run(procedure, CommandType.StoredProcedure, dbCommand -> {
            Set<String> strings = param.keySet();
            for (String string : strings) {
                dbCommand.addInParameter(string, param.get(string));
            }
            DbDataReader dbDataReader = dbCommand.executeReader();
            try {
                SqlRowSet sqlRowSet;
                for (int i = 0; i < dbDataReader.getRowSetCount(); i++) {
                    List<Map<String, Object>> contentList = new ArrayList<>();
                    sqlRowSet = dbDataReader.getSqlRowSet(i);
                    while (sqlRowSet.next()) {
                        Integer columnCount = sqlRowSet.getMetaData().getColumnCount();
                        Map<String, Object> map = new HashMap<>();
                        for (int j = 1; j <= columnCount; j++) {
                            Object value = sqlRowSet.getObject(sqlRowSet.getMetaData().getColumnLabel(j));
                            map.put(sqlRowSet.getMetaData().getColumnLabel(j), value);
                        }
                        contentList.add(map);
                    }
                    resultList.add(contentList);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            } finally {
                if (dbDataReader != null) {
                    dbDataReader.close();
                }
            }
        });
        return resultList;
    }


    public T getEntityByOneColumn(KeyValue<?> colKeyValue) {
        Value<T> res = new Value<>();
        SqlQueryMate sqlQueryMate = getSelectMate();
        sqlQueryMate.where(true, new KeyValueCondition(colKeyValue.key, ScriptConditionEnum.Eq, colKeyValue.value));
        String sql = sqlQueryMate.getSql();
        run(sql, CommandType.Text, dbCmd -> {
            for (DbParameter i : sqlQueryMate.getParameters()) {
                dbCmd.addParameter(i);
            }
            res.data = dbCmd.executeToEntity(getEntityClass());
        });
        return res.data;
    }

    public T getEntityByColumns(List<KeyValue> list) {
        Value<T> res = new Value<>();
        SqlQueryMate sqlQueryMate = getSelectMate();
        for (KeyValue colKeyValue : list) {
            sqlQueryMate.where(true, new KeyValueCondition(colKeyValue.key, ScriptConditionEnum.Eq, colKeyValue.value));
        }
        String sql = sqlQueryMate.getSql();
        run(sql, CommandType.Text, dbCmd -> {
            for (DbParameter i : sqlQueryMate.getParameters()) {
                dbCmd.addParameter(i);
            }
            res.data = dbCmd.executeToEntity(getEntityClass());
        });
        return res.data;
    }

    public List<T> getListEntityByColumns(List<KeyValue> list) {
        List<T> res = new ArrayList<>();
        SqlQueryMate sqlQueryMate = getSelectMate();
        for (KeyValue colKeyValue : list) {
            sqlQueryMate.where(true, new KeyValueCondition(colKeyValue.key, ScriptConditionEnum.Eq, colKeyValue.value));
        }
        String sql = sqlQueryMate.getSql();
        run(sql, CommandType.Text, dbCmd -> {
            for (DbParameter i : sqlQueryMate.getParameters()) {
                dbCmd.addParameter(i);
            }
            dbCmd.executeToList(res, getEntityClass());
        });
        return res;
    }

    public List<T> getListEntityByOneColumn(KeyValue<?> colKeyValue) {
        List<T> res = new ArrayList<>();
        SqlQueryMate sqlQueryMate = getSelectMate();
        sqlQueryMate.where(true, new KeyValueCondition(colKeyValue.key, ScriptConditionEnum.Eq, colKeyValue.value));
        String sql = sqlQueryMate.getSql();
        run(sql, CommandType.Text, dbCmd -> {
            for (DbParameter i : sqlQueryMate.getParameters()) {
                dbCmd.addParameter(i);
            }
            dbCmd.executeToList(res, getEntityClass());
        });
        return res;
    }

    public boolean updateByTable(String userId, List<KeyValue> setList, List<KeyValue> whereList) {

        DbTableMate dbTableMate = Utility.getDbTableMate(getEntityClass());
        SqlUpdateMate sqlUpdateMate = new SqlUpdateMate(dbTableMate.tableInfo.tableName());
        for (KeyValue colKeyValue : whereList) {
            sqlUpdateMate.where(true, new KeyValueCondition(colKeyValue.key, ScriptConditionEnum.Eq, colKeyValue.value));
        }
        for (KeyValue colKeyValue : setList) {
            sqlUpdateMate.setColumnValue(true, colKeyValue.key, colKeyValue.value);
        }
        sqlUpdateMate.setColumnValue(true, "c_db_last_update_id", userId);
        sqlUpdateMate.setColumnValue(true, "c_db_last_update_date", LocalDateTime.now());
        return updateSqlUpdateMate(sqlUpdateMate);
    }

    public boolean updateSqlUpdateMate(SqlUpdateMate updateMate) {
        Boolean[] result = new Boolean[]{Boolean.FALSE};
        run(updateMate.getSql(), CommandType.Text, dbCommand -> {
            updateMate.getParameters().forEach(dbCommand::addParameter);
            result[0] = dbCommand.executeNonQuery();
        });

        return result[0];
    }


}
